package com.example.daytest.exceltosql;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * *
 *
 * @description:
 * @param:
 * @return:
 * @author heyu
 * @date: 2021/5/13 下午2:12
 */
public class ExcelToSql {
  public static void executeInfo() throws IOException {

    File file1 =
        new File(
            "C:\\Users\\Administrator\\Desktop\\号卡数据割接\\号卡数据割接\\割接\\学生信息审核样本数据.xlsx"); // 指定本地的数据目录
    String slqPath = "C:\\Users\\Administrator\\Desktop\\testSql.sql"; // 指定生成文件目录
    try {
      InputStream in = new FileInputStream(file1.getAbsolutePath());
      Workbook wb = new XSSFWorkbook(in);
      in.close();
      String sql = "";
      Sheet sheetAt = wb.getSheetAt(0);
      int lastRowNum = sheetAt.getLastRowNum();

      List<ExcelBean> excelBean = getExcelBean(sql, sheetAt, lastRowNum);
      String sqla =
          "INSERT  INTO TD_STUDENT_AUDIT (ID,RESERVATION_PHONE,CARD_NUM ,STUDENT_NAME,SCHOOL_NAME,COLLEGE_NAME,PIC_URL,AUDIT_STATE,AUDIT_REMARK,CREATE_DATE,STUDENT_TAG_STATE,CITY_ID,STUDENT_PHONE,CARD_TYPE,EDUCATION_SYSTEM,GRADE,ORDER_ID) "
              + "VALUES\n\t";
      int count = 0;
      StringBuilder stringBuilder = new StringBuilder();
      for (ExcelBean bean : excelBean) {

        if (count % 3000 == 0) {
          stringBuilder.append(sqla);
        }
        stringBuilder.append("('" + UUID.randomUUID().toString().replaceAll("-", "") + "',");
        stringBuilder.append("'" + bean.getNum() + "',");
        stringBuilder.append("'" + bean.getId() + "',");
        stringBuilder.append("'" + bean.getName() + "',");
        stringBuilder.append("'" + bean.getSchool() + "',");
        stringBuilder.append("'" + bean.getCollege() + "',");
        stringBuilder.append("'" + bean.getNoticeimage() + "',");
        stringBuilder.append("'" + bean.getState() + "',");
        stringBuilder.append("'" + bean.getRemarks() + "',");
        stringBuilder.append("'" + bean.getCreattime() + "',");
        stringBuilder.append("'" + bean.getOverlying() + "',");
        stringBuilder.append("'" + bean.getCity() + "',");
        stringBuilder.append("'" + bean.getContactnumber() + "',");
        stringBuilder.append("'" + bean.getCerttype() + "',");
        stringBuilder.append("'" + bean.getEducation() + "',");
        stringBuilder.append("'" + bean.getAge() + "',");
        stringBuilder.append("'" + bean.getOrdernum() + "'),\n\t");
        count++;
      }

      File file = new File(slqPath);
      if (!file.exists()) {
        file.createNewFile();
      }
      byte[] bytes = stringBuilder.toString().getBytes();
      OutputStream os = new FileOutputStream(slqPath);
      os.write(bytes);
      os.flush();
      os.close();
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
  }

  /**
   * *
   *
   * @description:
   * @param: [sql, sheetAt, lastRowNum]
   * @return: java.util.List<com.example.daytest.exceltosql.ExcelBean>
   * @author heyu
   * @date: 2021/5/13 上午10:00
   */
  private static List<ExcelBean> getExcelBean(String sql, Sheet sheetAt, int lastRowNum) {
    List<ExcelBean> list = new ArrayList<>();
    for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
      ExcelBean bean = new ExcelBean(); // 创建一个单独Bean、

      System.out.println("rowNum = " + rowNum);
      Row row = sheetAt.getRow(rowNum);
      if (row != null) {
        Cell cell = row.getCell(0);
        if (cell != null) {
          row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
          bean.setNum(cell.getStringCellValue());
        }
        cell = row.getCell(1);
        if (cell != null) {
          bean.setId(cell.getStringCellValue());
        }
        cell = row.getCell(2);
        if (cell != null) {
          bean.setName(cell.getStringCellValue());
        }
        cell = row.getCell(3);
        if (cell != null) {
          bean.setSchool(cell.getStringCellValue());
        }
        cell = row.getCell(4);
        if (cell != null) {
          bean.setCollege(cell.getStringCellValue());
        }
        cell = row.getCell(6);
        if (cell != null) {
          bean.setNoticeimage(cell.getStringCellValue());
        }
        cell = row.getCell(7);
        if (cell != null) {
          row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
          bean.setState(cell.getStringCellValue());
        }
        cell = row.getCell(8);
        if (cell != null) {
          bean.setRemarks(cell.getStringCellValue());
        }
        cell = row.getCell(9);
        if (cell != null) {
          row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
          bean.setCreattime(cell.getStringCellValue());
        }
        cell = row.getCell(11);
        if (cell != null) {
          row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
          bean.setOverlying(cell.getStringCellValue());
        }
        cell = row.getCell(12);
        if (cell != null) {
          row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
          bean.setCity(cell.getStringCellValue());
        }
        cell = row.getCell(15);
        if (cell != null) {
          row.getCell(15).setCellType(Cell.CELL_TYPE_STRING);
          bean.setContactnumber(cell.getStringCellValue());
        }
        //                cell = row.getCell(19);
        //                if (cell != null) {
        //                    row.getCell(19).setCellType(Cell.CELL_TYPE_STRING);
        //                    bean.setCerttype(cell.getStringCellValue());
        //                }
        //                cell = row.getCell(20);
        //                if (cell != null) {
        //                    bean.setEducation(cell.getStringCellValue());
        //                }
        //                cell = row.getCell(21);
        //                if (cell != null) {
        //                    bean.setAge(cell.getStringCellValue());
        //                }
        //                cell = row.getCell(1);
        //                if (cell != null) {
        //                    bean.setOrdernum(cell.getStringCellValue());
        //                }
      }
      list.add(bean);
    }
    return list;
  }

  public static void main(String args[]) throws IOException {
    long startTime = System.currentTimeMillis();
    executeInfo();
    int a = 0;
    System.out.println("ddd");
    long endTime = System.currentTimeMillis();
    System.out.println("耗时为m:" + (endTime - startTime) / 1000);
  }
}
